USE [BMIG_Mensual_POST_CADENA]
GO

 -- creating the store procedure
IF EXISTS (SELECT name  FROM   sysobjects WHERE  name = N'[dbo].[WASP_M0014_FINAL_SEGURO_CESANTIA]' AND type = 'P')
    DROP PROCEDURE [dbo].[WASP_M0014_FINAL_SEGURO_CESANTIA]
GO



CREATE PROCEDURE [dbo].[WASP_M0014_FINAL_SEGURO_CESANTIA]
AS

BEGIN

		SELECT convert(numeric,max(a.ppcap + case  when b.ppimp9 is not null then  a.ppint- b.ppimp9
									else a.ppint end))  as VALCUO
			   , a.ppoper, a.ppsuc, a.ppcta
		
		INTO #valcuo
		
		FROM fsd601 as a
		LEFT JOIN fsd611 as b ON a.ppcta  = b.ppcta 
							and a.ppoper = b.ppoper 
							and	a.ppsuc = b.ppsuc 
							and	a.ppfpag = b.ppfpag 
		
		WHERE 
				--a.ppcta  *= b.ppcta and
				--a.ppoper *= b.ppoper and
				--a.ppsuc  *= b.ppsuc and
				--a.ppfpag *= b.ppfpag and
				b.ppexte = 0 and
				b.pptipo = 'M' 
		
		GROUP BY a.ppoper, a.ppsuc, a.ppcta
END


BEGIN
		SELECT a.ppsuc, a.ppoper, a.ppcta, REPLACE(CONVERT(VARCHAR,MAX(PPFPAG),105),'-','')AS FECHTER 
		
		INTO #FECHTER
		
		FROM fsd601 as a , sc_segcesa as b 
		
		WHERE a.ppsuc = b.ppsuc and
			  a.ppcta = b.ppcta and
			  a.ppoper = b.ppoper
	
		GROUP BY a.ppsuc, a.ppoper, a.ppcta
END


BEGIN
		SELECT 
			APELLIDO1, 
			APELLIDO2, 
			NOMBRE1, 
			NOMBRE2,
			RUT, 
			DV, 
			DIRTRABAJA, 
			COMUNATRAB,
			FECNAC, 
       		FECHAOTCRE, 
			convert(numeric,MONCRE) as MONCRE, 
			FOLIO,
			convert(numeric,VALCUO) VALCUO,  
			count(*) CUOTAS, 
			FECHTER,
			VAL_SEG = case when (a.aopap = 2 and a.aomda = 999)then	cast((VAL_SEG/100)* aopre as numeric)else cast(VAL_SEG as numeric)end,
			NUMCRED as NUMDECREDITO,
			FACREDITO AS FACREDITO,
			SUBSTRING (FECHTER,1,2) AS FVCUOTAS,
			b.ppsuc as AGENCOLOC
			
		INTO dbo.seguro_cesantia
		
		FROM seg_cesantia as a,
			 sc_segcesa as b,
			 #valcuo as c,
			 fsd601 as d,
			 #FECHTER as e
		
		WHERE b.ppsuc = a.aosuc  and
			  b.ppcta= a.RUT  and
			  b.ppoper = a.FOLIO and
			  b.ppsuc = c.ppsuc  and
			  b.ppcta= c.ppcta  and
			  b.ppoper = c.ppoper and
			  b.ppsuc = d.ppsuc  and
			  b.ppcta= d.ppcta  and
			  b.ppoper = d.ppoper and
			  b.ppsuc = e.ppsuc  and
			  b.ppcta= e.ppcta  and
			  b.ppoper = e.ppoper and
			  d601co = 'S' and
			  ppsbop = 0
			  
		GROUP BY b.ppsuc,FECHAS, APELLIDO1, APELLIDO2, NOMBRE1, NOMBRE2, RUT, DV, DIRTRABAJA, COMUNATRAB, 
				 FECNAC, FECHAOTCRE, MONCRE, FOLIO, VAL_SEG, VALCUO, FECHTER, AOPRE, AOPAP, AOMDA,NUMCRED,FACREDITO,FECHTER
END